//
//  DatabaseTestViewController.m
//  DatabaseTest
//
//  Created by wu di on 12-6-21.
//  Copyright 2012 UTS. All rights reserved.
//

#import "DatabaseTestViewController.h"

@implementation DatabaseTestViewController



/*
// The designated initializer. Override to perform setup that is required before the view is loaded.
- (id)initWithNibName:(NSString *)nibNameOrNil bundle:(NSBundle *)nibBundleOrNil {
    self = [super initWithNibName:nibNameOrNil bundle:nibBundleOrNil];
    if (self) {
        // Custom initialization
    }
    return self;
}
*/

/*
// Implement loadView to create a view hierarchy programmatically, without using a nib.
- (void)loadView {
}
*/

-(NSString *) filePath {
  /*	NSArray *paths = NSSearchPathForDirectoriesInDomains(
						NSDocumentDirectory, NSUserDomainMask, YES);
	NSString *documentsDir = [paths objectAtIndex:0];
	return [documentsDir stringByAppendingPathComponent:@"database.sql"];*/
        NSString *p = [[NSBundle mainBundle] pathForResource:@"database" ofType:@"sql"];
        return p;
}


-(void) closeDB {
	sqlite3_close(db);	
}

-(void) openDB {
	//---create database---
	if (sqlite3_open([[self filePath] UTF8String], &db) != SQLITE_OK )
	{
		sqlite3_close(db);
		NSAssert(0, @"Database failed to open.");
	}	
}


-(void) createTableNamed:(NSString *) tableName withField1:(NSString *) field1 withField2:(NSString *) field2 {
	char *err;
	NSString *sql = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS '%@' ('%@' TEXT PRIMARY KEY, '%@' TEXT);", tableName, field1, field2];
	
	if (sqlite3_exec(db, [sql UTF8String], NULL, NULL, &err) != SQLITE_OK)
	{
		sqlite3_close(db);
		NSAssert(0, @"Tabled failed to create.");
	}
}

-(void) getAllRowsFromTableNamed: (NSString *) tableName
{
  NSString *qsql = [[NSString alloc] initWithFormat:@"SELECT * FROM %@",tableName];
  sqlite3_stmt *statement;
  
  if (sqlite3_prepare_v2( db, [qsql UTF8String], -1, &statement, nil) == SQLITE_OK)
    {
      while (sqlite3_step(statement) == SQLITE_ROW)
        {
          char *field1 = (char *) sqlite3_column_text(statement, 0);
          NSString *field1Str = [[NSString alloc] initWithUTF8String: field1];
			
          char *field2 = (char *) sqlite3_column_text(statement, 1); 
          NSString *field2Str = [[NSString	alloc] initWithUTF8String: field2];
			
          NSString *str = [[NSString alloc] initWithFormat:@"%@ - %@", field1Str, field2Str];
          NSLog(str);
			
          [field1Str release];
          [field2Str release];
          [str release];
        }
      // Release the compiled statement from memory
      sqlite3_finalize(statement);		
    }
}

-(void) insertIntoDB: (NSString *)tableName
          withField1: (NSString *) field1 field1Value:(NSString *) field1Value
           andField2: (NSString *) field2 field2Value:(NSString *) field2Value
{
  NSString *sqlStr = [NSString stringWithFormat: @"INSERT OR REPLACE INTO '%@' ('%@', '%@') VALUES (?,?)", 
                               tableName, field1, field2];
  const char *sql = [sqlStr UTF8String];
  sqlite3_stmt *statement;	

  if (sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK) {
    sqlite3_bind_text(statement, 1, [field1Value UTF8String], -1, NULL);
    sqlite3_bind_text(statement, 2, [field2Value UTF8String], -1, NULL);
  }
	
  if (sqlite3_step(statement) != SQLITE_DONE)
    NSAssert(0, @"Error updating table.");
	
  sqlite3_finalize(statement);	
}

// Implement viewDidLoad to do additional setup after loading the view, typically from a nib.
- (void)viewDidLoad {
    [super viewDidLoad];
    [self openDB];
    [self createTableNamed:@"mydb" withField1:@"name" withField2:@"email"];

    for(int i=0;i<=2;i++)
      {
        NSString *email = [[NSString alloc] initWithFormat: @"user%d@apple.com",i];
        NSString *name = [[NSString alloc] initWithFormat:@"user %d",i];
        [self insertIntoDB:@"mydb" withField1:@"email" field1Value:email andField2:@"name" field2Value:name];
      }

    [self getAllRowsFromTableNamed:@"mydb"];
    
}



/*
// Override to allow orientations other than the default portrait orientation.
- (BOOL)shouldAutorotateToInterfaceOrientation:(UIInterfaceOrientation)interfaceOrientation {
    // Return YES for supported orientations
    return (interfaceOrientation == UIInterfaceOrientationPortrait);
}
*/

- (void)didReceiveMemoryWarning {
	// Releases the view if it doesn't have a superview.
    [super didReceiveMemoryWarning];
	
	// Release any cached data, images, etc that aren't in use.
}

- (void)viewDidUnload {
	// Release any retained subviews of the main view.
	// e.g. self.myOutlet = nil;
}


- (void)dealloc {
    [super dealloc];
}

@end
